package org.gw.building.repositories;

import org.gw.building.model.ClassInfo;
import org.gw.building.model.StuInfo;
import org.springframework.cache.annotation.CacheEvict;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

public interface StuInfoRepository extends JpaRepository<StuInfo, String> {
	@Query("from StuInfo s where s.user.uid=?1")
	@Cacheable(value = "train.StuInfo")
	public List<StuInfo> findByXh(String xh);

	@Query("from StuInfo s where s.user.uid=?1 and s.term.id = ?2")
	@Cacheable(value = "train.StuInfo")
	public StuInfo findOneByXhAndTerm(String xh, String trem);

	@CacheEvict(value = "train.StuInfo", allEntries = true)
	public StuInfo save(StuInfo entity);

	@Query("from StuInfo s where (?1 is null or s.term.id=?1) and (?2 is null or s.user.name like ?2 or s.user.uid like ?2) and (?3 is null or s.user.grade=?3 ) and (?4 is null or s.user.department=?4) and (?5 is null or s.classInfo.id=?5)"
			+ " and (?6 is null or  s.sfsqtzs=?6) and (?7 is null or  s.sfwtzs=?7 ) and (?8 is null or  s.status=?8 ) and (?9 is null or s.xq=?9)")
	public Page<StuInfo> queryStudents(String term, String name, String grade, String department, String clazz,
                                       Integer sfsqtzs, Integer sftzs, Integer status, String xq, Pageable page);

	@Query("from StuInfo s where (?1 is null or s.term.id=?1) and s.classInfo is null")
	public Page<StuInfo> queryDispatchStudents(String term, Pageable page);

	@Query("select count(s) from StuInfo s where (?1 is null or s.term.id=?1)")
	public long countByTerm(String term);

	@Query("select count(s) from StuInfo s where (?1 is null or s.term.id=?1)  and s.classInfo is not null")
	public long countDispatchedByTerm(String term);

	@Query("select s.id,s.classInfo.id,s.user.grade ,s.user.department ,s.xq,s.sfwtzs from StuInfo s where (?1 is null or s.term.id=?1)")
	public List<Object[]> getAllByTerm(String term);

	@Query("update StuInfo s set s.classInfo=?2 where s.id in ?1")
	@Modifying()
	@Transactional
	public int updateClassInIds(List<String> ids, ClassInfo classInfo);
}
